from flask import Flask, jsonify, request, render_template
from flask_cors import CORS
import json
import os
import pandas as pd
import modebase.config as cfg
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import Boolean, Float  # 添加这一行，导入 Boolean 和 Float 类型
from flask import session, redirect, url_for
from modebase.access_manager import AccessDBManager
app = Flask(__name__)
CORS(app)
app.secret_key = 'your_secret_key_here'
app.config['SEND_FILE_MAX_AGE_DEFAULT'] = 31536000  # 1 year cache for static files
# 获取当前文件所在目录
current_dir = os.path.dirname(os.path.abspath(__file__))
# 构建数据库文件的路径
db_path = os.path.join(current_dir, 'database', 'test.db')
app.config['SQLALCHEMY_DATABASE_URI'] = f'sqlite:///{db_path}'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

# 定义IPConfig数据库模型

# 定义用户表模型
class User(db.Model):
    __tablename__ = 'users'
    
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(100), nullable=False, unique=True)
    password = db.Column(db.String(100), nullable=False)
    
    def verify_password(self, password):
        return self.password == password

class UpdateTime(db.Model):
    __tablename__ = 'update_time'
    
    id = db.Column(db.Integer, primary_key=True)
    modele_name = db.Column(db.String(100), nullable=False, unique=True)  # 功能模块名称，唯一约束
    last_updated = db.Column(db.DateTime, nullable=True)  # 最后更新时间
    remarks = db.Column(db.String(255), nullable=True)    # 新增备注文本列，可为空
    
    def to_dict(self):
        return {
            'id': self.id,
            'modele_name': self.modele_name,
            'last_updated': self.last_updated.strftime('%Y-%m-%d %H:%M:%S'),
            'remarks': self.remarks
        }

class IPConfig(db.Model):
    __tablename__ = 'ip_config'
    
    id = db.Column(db.Integer, primary_key=True)
    workshop=db.Column(db.String(100), nullable=False)  # 车间
    size=db.Column(db.String(100), nullable=False) #产品尺寸
    line = db.Column(db.String(100), nullable=False)  # 线别，唯一约束
    ip = db.Column(db.String(100), nullable=False, unique=True)    # IP地址，唯一约束
    db_path = db.Column(db.String(255), nullable=False)  # 数据库路径
    grade_path = db.Column(db.String(255), nullable=False)  # 分档路径
    active_line_grade = db.Column(db.String(255), nullable=False)  # 在用产线分档
    enabled = db.Column(db.Boolean, default=False)  # 是否启用
    # 对切是否完成，Boolean 类型，对应 0 或 1
    cutover_completed = db.Column(Boolean, default=False)
    # 开班 FF，保留 5 位小数，可为空
    opening_ff = db.Column(Float(precision=5), nullable=True)
    # 开班 UOC，保留 5 位小数，可为空
    opening_uoc = db.Column(Float(precision=5), nullable=True)
    # 开班 iSC，保留 5 位小数，可为空
    opening_isc = db.Column(Float(precision=5), nullable=True)
    # 当前 FF，保留 5 位小数，可为空
    current_ff = db.Column(Float(precision=5), nullable=True)
    # 当前 UOC，保留 5 位小数，可为空
    current_uoc = db.Column(Float(precision=5), nullable=True)
    # 当前 ISC，保留 5 位小数，可为空
    current_isc = db.Column(Float(precision=5), nullable=True)
    # 影响效率，保留 5 位小数，可为空
    efficiency_impact = db.Column(Float(precision=5), nullable=True)
    def to_dict(self):
        return {
            'id': self.id,
            'workshop': self.workshop,
            'size': self.size,
            'line': self.line,
            'ip': self.ip,
            'db_path': self.db_path,
            'grade_path': self.grade_path,
            'active_line_grade': self.active_line_grade,
            'enabled': self.enabled,
            'cutover_completed': self.cutover_completed,
            'opening_ff': self.opening_ff,
            'opening_uoc': self.opening_uoc,
            'opening_isc': self.opening_isc,
            'current_ff': self.current_ff,
            'current_uoc': self.current_uoc,
            'current_isc': self.current_isc,
            'efficiency_impact': self.efficiency_impact
        }

class alldata(db.Model):
    __tablename__ = 'alldata'
    
    id = db.Column(db.Integer, primary_key=True)
    BinFileName = db.Column(db.String(100), nullable=True)
    avg_eta = db.Column(db.String(100), nullable=True)
    ru_eta = db.Column(db.String(100), nullable=True)
    hbd=db.Column(db.String(100), nullable=True)
    sum_IvGrade=db.Column(db.String(100), nullable=True)
    IRev2_B=db.Column(db.String(100), nullable=True)
    IRev2_Fail=db.Column(db.String(100), nullable=True)
    dlcc=db.Column(db.String(100), nullable=True)
    shift = db.Column(db.String(100), nullable=True)
    date = db.Column(db.String(100), nullable=True)
    
    
    
    def to_dict(self):
        return {
            'id': self.id,
            'BinFileName': self.BinFileName,
            'avg_eta': self.avg_eta,
            'ru_eta': self.ru_eta,
            'hbd': self.hbd,
            'sum_IvGrade': self.sum_IvGrade,
            'IRev2_B': self.IRev2_B,
            'IRev2_Fail': self.IRev2_Fail,
            'dlcc': self.dlcc,
            'shift': self.shift,    
            'date': self.date,
            
        }
class GearData(db.Model):
    __tablename__ = 'GearData'
    
    id = db.Column(db.Integer, primary_key=True)
    BinFileName = db.Column(db.String(100), nullable=True)
    Class = db.Column(db.String(100), nullable=True)
    IvGrade = db.Column(db.String(100), nullable=True)
    Aoi1R = db.Column(db.String(100), nullable=True)
    sum_IvGrade = db.Column(db.Float(precision=5), nullable=True)
    shift = db.Column(db.String(100), nullable=True)
    date = db.Column(db.String(100), nullable=True)
    
    def to_dict(self):
        return {
            'id': self.id,
            'BinFileName': self.BinFileName,
            'Class': self.Class,
            'IvGrade': self.IvGrade,
            'Aoi1R': self.Aoi1R,
            'sum_IvGrade': self.sum_IvGrade,
            'shift': self.shift,
            'date': self.date
        }
class GearData1(db.Model):
    __tablename__ = 'GearData1'
    
    id = db.Column(db.Integer, primary_key=True)
    BinFileName = db.Column(db.String(100), nullable=True)
    Class = db.Column(db.String(100), nullable=True)
    IvGrade = db.Column(db.String(100), nullable=True)
    Aoi1R = db.Column(db.String(100), nullable=True)
    sum_IvGrade = db.Column(db.Float(precision=5), nullable=True)
    eta1 = db.Column(db.Float(precision=5), nullable=True)
    shift = db.Column(db.String(100), nullable=True)
    date = db.Column(db.String(100), nullable=True)
    
    def to_dict(self):
        return {
            'id': self.id,
            'BinFileName': self.BinFileName,
            'Class': self.Class,
            'IvGrade': self.IvGrade,
            'Aoi1R': self.Aoi1R,
            'sum_IvGrade': self.sum_IvGrade,
            'eta1': self.eta1,
            'shift': self.shift,
            'date': self.date
        }
class sumbin(db.Model):
    __tablename__ = 'sumbin'
    
    id = db.Column(db.Integer, primary_key=True)
    BinFileName = db.Column(db.String(100), nullable=True)
    binn = db.Column(db.String(100), nullable=True)
    quantity = db.Column(db.Integer, nullable=True)
    shift = db.Column(db.String(100), nullable=True)
    date = db.Column(db.String(100), nullable=True)
    
    def to_dict(self):
        return {
            'id': self.id,
            'BinFileName': self.BinFileName,
            'binn': self.binn,
            'quantity': self.quantity,
            'shift': self.shift,
            'date': self.date
        }
class avgeta(db.Model):
    __tablename__ = 'avgeta'
    
    id = db.Column(db.Integer, primary_key=True)
    date = db.Column(db.String(100), nullable=True)  # 新增日期字段，存储日期信息，可为空
    shift = db.Column(db.String(100), nullable=True)   # 新增班次字段，存储班次信息，可为空
    BinFileName = db.Column(db.String(100), nullable=True)  # 新增产品名称字段，存储产品名称信息，可为空
    line = db.Column(db.String(100), nullable=True)  # 线别，唯一约束
    quantity = db.Column(db.Integer, nullable=True)  # 数量
    eta1 = db.Column(db.Float(precision=5), nullable=True)  # Eta
    uoc1 = db.Column(db.Float(precision=5), nullable=True)  # Uoc
    isc1 = db.Column(db.Float(precision=5), nullable=True)  # Isc
    ff1 = db.Column(db.Float(precision=5), nullable=True)  # FF
    rs = db.Column(db.Float(precision=5), nullable=True)  # Rs
    rsh = db.Column(db.Float(precision=5), nullable=True)  # Rsh
    irev2_lt_0 = db.Column(db.Float(precision=5), nullable=True)  # IRev2 < 0
    irev2_lt_0_05 = db.Column(db.Float(precision=5), nullable=True)  # IRev2 < 0.05
    irev2_lt_0_2 = db.Column(db.Float(precision=5), nullable=True)  # IRev2 < 0.2
    irev2_lt_0_3 = db.Column(db.Float(precision=5), nullable=True)  # IRev2 < 0.3
    def to_dict(self):
        return {
            'id': self.id,
            'date': self.date,  # 新增
            'shift': self.shift,  # 新增
            'line': self.line,
            'quantity': self.quantity,
            'BinFileName': self.BinFileName,
            'eta1': self.eta1,
            'uoc1': self.uoc1,
            'isc1': self.isc1,
            'ff1': self.ff1,
            'rs': self.rs,
            'rsh': self.rsh,
            'irev2_lt_0': self.irev2_lt_0,
            'irev2_lt_0_05': self.irev2_lt_0_05,
            'irev2_lt_0_2': self.irev2_lt_0_2,
            'irev2_lt_0_3': self.irev2_lt_0_3
        }

@app.route('/config/ipconfig')
def config_ipconfig():
    return render_template('config/ipconfig.html')

# API接口：获取所有IP配置
@app.route('/api/ipconfig', methods=['GET'])
def get_ip_configs():
    print("获取所有IP配置")
    workshop = request.args.get('workshop')
    size = request.args.get('size')
    query = IPConfig.query
    if workshop:
        query = query.filter(IPConfig.workshop == workshop)
    if size and size != "全部":
        query = query.filter(IPConfig.size == size)
    ip_configs = query.all()
    return jsonify([config.to_dict() for config in ip_configs])

# API接口：添加或更新IP配置
@app.route('/api/ipconfig', methods=['POST'])
def add_update_ip_config():
    data = request.json
    
    # 如果提供了ID，则更新现有记录
    if 'id' in data and data['id']:
        ip_config = IPConfig.query.get(data['id'])
        if not ip_config:
            return jsonify({'error': '未找到指定的IP配置记录'}), 404
    else:
        # 否则创建新记录
        ip_config = IPConfig()
    
    # 检查是否有其他记录使用相同的线别或IP（排除当前记录）
    # line_exists = IPConfig.query.filter(IPConfig.line == data.get('line', ''), IPConfig.id != getattr(ip_config, 'id', None)).first()
    ip_exists = IPConfig.query.filter(IPConfig.ip == data.get('ip', ''), IPConfig.id != getattr(ip_config, 'id', None)).first()
    
    # if line_exists:
    #     return jsonify({'error': f'线别 "{data.get("line", "")}" 已存在'}), 400
    
    if ip_exists:
        return jsonify({'error': f'IP地址 "{data.get("ip", "")}" 已存在'}), 400
    
    print(data)
    # 更新字段
    ip_config.workshop = data.get('workshop', '')
    ip_config.size = data.get('size', '')
    ip_config.line = data.get('line', '')
    ip_config.ip = data.get('ip', '')
    ip_config.db_path = data.get('db_path', '')
    ip_config.grade_path = data.get('grade_path', '')
    ip_config.active_line_grade = data.get('active_line_grade', '')
    ip_config.enabled = data.get('enabled', False)
    
    try:
        # 保存到数据库
        db.session.add(ip_config)
        db.session.commit()
        return jsonify(ip_config.to_dict())
    except Exception as e:
        db.session.rollback()
        return jsonify({'error': f'保存失败: {str(e)}'}), 500

# API接口：删除IP配置
@app.route('/api/ipconfig', methods=['DELETE'])
def delete_ip_config():
    data = request.json
    if 'id' not in data:
        return jsonify({'error': '未提供ID'}), 400
    
    ip_config = IPConfig.query.get(data['id'])
    if not ip_config:
        return jsonify({'error': '未找到指定的IP配置记录'}), 404
    
    db.session.delete(ip_config)
    db.session.commit()
    
    return jsonify({'success': True})

# API接口：获取所有每日统计数据
@app.route('/api/daily_stats', methods=['GET'])
def get_daily_stats():
    stats = alldata.query.all()
    print(stats)
    return jsonify([stat.to_dict() for stat in stats])

# API接口：添加或更新每日统计数据
@app.route('/api/daily_stats', methods=['POST'])
def add_update_daily_stats():
    data = request.json
    if 'id' in data and data['id']:
        stat = alldata.query.get(data['id'])
        if not stat:
            return jsonify({'error': '未找到指定的记录'}), 404
    else:
        stat = alldata()

    stat.BinFileName = data.get('BinFileName', '')
    stat.avg_eta = data.get('avg_eta', '')
    stat.ru_eta = data.get('ru_eta', '')
    stat.hbd = data.get('hbd', '')
    stat.sum_IvGrade = data.get('sum_IvGrade', '')
    stat.IRev2_B = data.get('IRev2_B', '')
    stat.IRev2_Fail = data.get('IRev2_Fail', '')
    stat.dlcc = data.get('dlcc', '')
    stat.shift = data.get('shift', '')
    stat.date = data.get('date', '')

    try:
        db.session.add(stat)
        db.session.commit()
        return jsonify(stat.to_dict())
    except Exception as e:
        db.session.rollback()
        return jsonify({'error': f'保存失败: {str(e)}'}), 500

# API接口：删除每日统计数据
@app.route('/api/daily_stats', methods=['DELETE'])
def delete_daily_stats():
    data = request.json
    if 'id' not in data:
        return jsonify({'error': '未提供ID'}), 400
    
    stat = alldata.query.get(data['id'])
    if not stat:
        return jsonify({'error': '未找到指定的记录'}), 404
    
    db.session.delete(stat)
    db.session.commit()
    
    return jsonify({'success': True})

# # API接口：查询单刷数据
@app.route('/api/query', methods=['POST'])
def query_data():
    # 获取并打印表单数据
    data = request.json
    if not data:
        return jsonify({'error': '未提供有效的数据'}), 400
    year = data["year"]
    month = data["month"]
    day = data["day"]
    line = data["line"]
    shift = data["shift"]
    print(f'查询数据: 年={year}, 月={month}, 日={day}, 线别={line}, 班次={shift}')

    # 根据线别查询IP配置
    ip_config = IPConfig.query.filter_by(line=line).first()
    if not ip_config:
        return jsonify({'error': f'未找到线别 {line} 的配置'}), 404

    # 拼接数据库查询路径
    db_query_path = f'\\\{ip_config.ip}{ip_config.db_path}\{year}-{month}-{day}-{line}-{shift}.mdb'
    access_db_manager = AccessDBManager(db_query_path)
    if access_db_manager.connect():
        try:
            # 执行 SQL 查询
            dq = pd.read_sql_query(cfg.access_sql_danshua, access_db_manager.conn)
            # 将查询结果转换为 JSON 格式并返回给浏览器
            result = dq.to_json(orient='records')
            print('转换前数据类型:', type(result))
            # 确保 result 不为 None 再进行转换
            if result is not None:
                json_result = json.loads(result)
            else:
                json_result = []
            print('转换后数据类型:', type(json_result))
            return jsonify(json_result)
        except Exception as e:
            print(f"执行 SQL 查询失败: {e}")
        finally:
            # 关闭数据库连接
            access_db_manager.close() 
    print(f'数据库查询路径: {db_query_path}')
    return jsonify({'error': '数据库连接失败'}), 500



@app.before_request
def check_login():
    """
    此函数在每个请求处理之前执行，用于检查用户是否已登录。
    对于某些特定的 API 端点，如果用户未登录，则会重定向到登录页面。
    """
    # 检查请求的端点是否存在
    if request.endpoint:
        # 检查端点是否以 'api_' 开头，用户是否未登录，并且端点不是 'get_ip_configs'
        # if request.endpoint.startswith('api_') and 'logged_in' not in session and request.endpoint != 'get_ip_configs':
        if request.endpoint.startswith('api_') and 'logged_in' not in session:
            # 如果满足上述条件，将用户重定向到登录页面
            return redirect(url_for('user_login'))

@app.route('/')
def index():
    if 'logged_in' not in session:
        return redirect(url_for('user_login'))
    username = session.get('username', '')
    return render_template('index.html', username=username)

@app.route('/user/login', methods=['GET', 'POST'])
def user_login():
    if request.method == 'POST':
        username = request.form.get('username')
        password = request.form.get('password')
        
        # 从用户表验证用户名和密码
        user = User.query.filter_by(username=username).first()
        if user and user.verify_password(password):
            session['logged_in'] = True
            session['username'] = username
            return redirect(url_for('index'))
        else:
            return redirect(url_for('user_login', err='用户名或密码错误'))
    
    return render_template('user/login.html')

@app.route('/logout')
def logout():
    session.pop('logged_in', None)
    return redirect(url_for('user_login'))

@app.route('/view/feidang')
def view_feidang():
    return render_template('view/feidang.html')

@app.route('/view/ClassStatistics')
def view_ClassStatistics():
    return render_template('view/ClassStatistics.html')

@app.route('/view/duiqiemoni.html')
def view_duiqiemoni():
    return render_template('view/duiqiemoni.html')

@app.route('/view/danshua.html')
def view_danshua():
    return render_template('view/danshua.html')

@app.route('/view/showefficiency.html')
def view_showefficiency():
    try:
        # 读取 alldata.json 文件
        with open('static/json/alldata.json', 'r', encoding='utf-8') as file:
            data = json.load(file)
        
        # 假设 data 是一个列表，每个元素是一个字典，查找 name=product_name 的值
        product_names = []
        for item in data:
            if item.get('name') == 'product_name':
                # 从 item 中获取 'value' 字段的值，如果不存在则默认为空字符串，然后按逗号分割成数组
                product_names = item.get('value', '').split(',')
                break
        print(product_names)
        # 渲染模板并传递产品名称列表
        return render_template('view/showefficiency.html', product_names=product_names)
    
    except Exception as e:
        print(f"读取文件时出错: {e}")
        return render_template('view/showefficiency.html', product_names=[])
@app.route('/view/proportion.html')
def view_proportion():
    return render_template('view/proportion.html')

@app.route('/view/proportioneta.html')
def view_proportioneta():
    try:
        # 读取 alldata.json 文件
        with open('static/json/alldata.json', 'r', encoding='utf-8') as file:
            data = json.load(file)
        
        # 假设 data 是一个列表，每个元素是一个字典，查找 name=product_name 的值
        product_names = []
        for item in data:
            if item.get('name') == 'product_name':
                # 从 item 中获取 'value' 字段的值，如果不存在则默认为空字符串，然后按逗号分割成数组
                product_names = item.get('value', '').split(',')
                break
        print(product_names)
        # 渲染模板并传递产品名称列表
        return render_template('view/proportioneta.html', product_names=product_names)
    
    except Exception as e:
        print(f"读取文件时出错: {e}")
        return render_template('view/proportioneta.html', product_names=[])

@app.route('/config/daily_stats.html')
def config_daily_stats():
    return render_template('config/daily_stats.html')

@app.route('/config/tableconfig.html')
def config_tableconfig():
    return render_template('config/tableconfig.html')

@app.route('/view/books.html')
def view_books():
    return render_template('view/books.html')

@app.route('/api/query_efficiency', methods=['POST'])
def query_efficiency():
    # try:
    data = request.get_json()
    query_date = data.get('query_date')
    shift = data.get('shift')
    product=data.get('product')
    print(f"Received query for date: {query_date}, shift: {shift}, product: {product}")
    # 查询avgeta表获取所有数据
    # avgeta_all_data = avgeta.query.all()
    # print(avgeta_all_data)
    avgeta_data = avgeta.query.filter_by(date=query_date, shift=shift,BinFileName=product).all()
    # print(avgeta_data)
    avgeta_result = [item.to_dict() for item in avgeta_data]
    # print(avgeta_result)
    # 查询update_time表
    update_time_data = UpdateTime.query.filter_by(modele_name='avgeta').first()
    # print(update_time_data)
    update_time_result = update_time_data.to_dict() if update_time_data else {}
    # print(update_time_result)
    return jsonify({
        'avgeta': avgeta_result,
        'update_time': update_time_result
    }), 200

    # except Exception as e:
    #     return jsonify({'error': str(e)}), 500

@app.route('/api/query_dwzb', methods=['POST'])
def query_dwzb():
    # try:
    data = request.get_json()
    print(data)
    query_date = data.get('query_date')
    shift = data.get('shift')
    product=data.get('product')
    print(f"浏览器数据: {query_date}, shift: {shift}, product: {product}")
    # 查询avgeta表获取所有数据
    # GearData_data = GearData.query.filter_by(date=query_date, shift=shift,BinFileName=product).all()
    # GearData_result = [item.to_dict() for item in GearData_data]
    # 按Class和IvGrade聚合，计算sum_IvGrade的总和
    aggregated_data = db.session.query(GearData.Class, GearData.IvGrade, db.func.sum(GearData.sum_IvGrade).label('total_sum_ivgrade'))\
        .filter_by(date=query_date, shift=shift, BinFileName=product)\
        .group_by(GearData.Class, GearData.IvGrade).all()
    aggregated_result = [{'Class': item.Class, 'IvGrade': item.IvGrade, 'total_sum_ivgrade': item.total_sum_ivgrade} for item in aggregated_data]
    # print(aggregated_result)
    # 按Class和AOI聚合，计算sum_IvGrade的总和
    aggaoi_data = db.session.query(GearData.Class, GearData.Aoi1R, db.func.sum(GearData.sum_IvGrade).label('total_sum_ivgrade'))\
        .filter_by(date=query_date, shift=shift, BinFileName=product)\
        .group_by(GearData.Class, GearData.Aoi1R).all()
    aggaoi_result = [{'Class': item.Class, 'Aoi1R': item.Aoi1R, 'total_sum_ivgrade': item.total_sum_ivgrade} for item in aggaoi_data]
    # print( aggaoi_result)
    #获取更新时间数据库
    update_time_data = UpdateTime.query.filter_by(modele_name='dangwei').first()
    update_time_result = update_time_data.to_dict() if update_time_data else {}
    return jsonify({
        'aggregated_data': aggregated_result,
        'aggaoi_data': aggaoi_result,
        'update_time': update_time_result
    }), 200

    # except Exception as e:
    #     return jsonify({'error': str(e)}), 500

@app.route('/api/query_dwzb_eta', methods=['POST'])
def query_dwzb_eta():
    # try:
    data = request.get_json()
    print(data)
    query_date = data.get('query_date')
    shift = data.get('shift')
    product=data.get('product')
    print(f"浏览器数据: {query_date}, shift: {shift}, product: {product}")
    # 查询avgeta表获取所有数据
    # GearData_data = GearData.query.filter_by(date=query_date, shift=shift,BinFileName=product).all()
    # GearData_result = [item.to_dict() for item in GearData_data]
    
    #1.查询alldata表获取最新7个数据
    all_datas = alldata.query.filter_by(BinFileName=product).order_by(alldata.date.desc(), alldata.shift.desc()).limit(30).all()
    all_data_result = [item.to_dict() for item in all_datas]

    # 按Class和IvGrade聚合，计算sum_IvGrade的总和
    aggregated_data = db.session.query(GearData1.Class, GearData1.IvGrade, db.func.sum(GearData1.sum_IvGrade).label('total_sum_ivgrade'))\
        .filter_by(date=query_date, shift=shift, BinFileName=product)\
        .group_by(GearData1.Class, GearData1.IvGrade).all()
    aggregated_result = [{'Class': item.Class, 'IvGrade': item.IvGrade, 'total_sum_ivgrade': item.total_sum_ivgrade} for item in aggregated_data]
    # print(aggregated_result)
    # 按Class和AOI聚合，计算sum_IvGrade的总和
    aggaoi_data = db.session.query(GearData1.Class, GearData1.Aoi1R, db.func.sum(GearData1.sum_IvGrade).label('total_sum_ivgrade'))\
        .filter_by(date=query_date, shift=shift, BinFileName=product)\
        .group_by(GearData1.Class, GearData1.Aoi1R).all()
    aggaoi_result = [{'Class': item.Class, 'Aoi1R': item.Aoi1R, 'total_sum_ivgrade': item.total_sum_ivgrade} for item in aggaoi_data]
    
    sumbin_data = db.session.query(sumbin.BinFileName,sumbin.binn,sumbin.quantity)\
        .filter_by(date=query_date, shift=shift, BinFileName=product)\
        .group_by(sumbin.binn).all()
    sumbin_result = [{'BinFileName': item.BinFileName, 'binn': item.binn, 'quantity': item.quantity} for item in  sumbin_data]
    # 原代码中存在错误，使用了未定义的 df，应改为 db.func
    # 聚合数据
    aggtj_data = db.session.query(GearData1.Class, db.func.sum(GearData1.sum_IvGrade).label('total_sum_ivgrade'), (db.func.sum(GearData1.sum_IvGrade * GearData1.eta1) / db.func.sum(GearData1.sum_IvGrade)).label('etacs'))\
        .filter_by(date=query_date, shift=shift, BinFileName=product)\
        .group_by(GearData1.Class).all()
    aggtj_result = [{'Class': item.Class, 'total_sum_ivgrade': item.total_sum_ivgrade, 'etacs': item.etacs} for item in aggtj_data]
    # print( aggaoi_result)
    #获取更新时间数据库
    update_time_data = UpdateTime.query.filter_by(modele_name='dangwei').first()
    update_time_result = update_time_data.to_dict() if update_time_data else {}
    return jsonify({
        'aggregated_data': aggregated_result,
        'aggaoi_data': aggaoi_result,
        'aggtj_data': aggtj_result,
        'sumbin_data':sumbin_result,
        'all_data':all_data_result,
        'update_time': update_time_result
    }), 200


JSON_FILE_PATH = os.path.join(app.root_path, 'static', 'json', 'alldata.json')

# 查找json数据
@app.route('/data', methods=['GET'])
def get_data():
    try:
        with open(JSON_FILE_PATH, 'r', encoding='utf-8') as file:
            data = json.load(file)
        return jsonify(data)
    except Exception as e:
        return jsonify({"error": str(e)}), 500

# 根据 id 查找单个数据
@app.route('/data/<int:item_id>', methods=['GET'])
def get_single_data(item_id):
    try:
        with open(JSON_FILE_PATH, 'r', encoding='utf-8') as file:
            data = json.load(file)
        for item in data:
            if item['id'] == item_id:
                return jsonify(item)
        return jsonify({"error": "未找到对应数据"}), 404
    except Exception as e:
        return jsonify({"error": str(e)}), 500

# 更新json数据
@app.route('/data/<int:item_id>', methods=['PUT'])
def update_data(item_id):
    try:
        new_data = request.get_json()
        with open(JSON_FILE_PATH, 'r', encoding='utf-8') as file:
            data = json.load(file)
        
        for index, item in enumerate(data):
            if item['id'] == item_id:
                data[index] = {**item, **new_data}
                with open(JSON_FILE_PATH, 'w', encoding='utf-8') as file:
                    json.dump(data, file, ensure_ascii=False, indent=4)
                return jsonify(data[index])
        
        return jsonify({"error": "未找到对应数据"}), 404
    except Exception as e:
        return jsonify({"error": str(e)}), 500

# 删除json数据
@app.route('/data/<int:item_id>', methods=['DELETE'])
def delete_data(item_id):
    try:
        with open(JSON_FILE_PATH, 'r', encoding='utf-8') as file:
            data = json.load(file)
        
        new_data = [item for item in data if item['id'] != item_id]
        
        if len(new_data) == len(data):
            return jsonify({"error": "未找到对应数据"}), 404
        
        with open(JSON_FILE_PATH, 'w', encoding='utf-8') as file:
            json.dump(new_data, file, ensure_ascii=False, indent=4)
        
        return jsonify({"message": "数据删除成功"})
    except Exception as e:
        return jsonify({"error": str(e)}), 500
# 添加json数据
@app.route('/data', methods=['POST'])
def add_data():
    try:
        new_item = request.get_json()
        print(new_item)
        if not new_item:
            return jsonify({"error": "请求体不能为空"}), 400
        
        with open(JSON_FILE_PATH, 'r', encoding='utf-8') as file:
            data = json.load(file)
        
        # 生成新的 id
        if data:
            new_id = max(item['id'] for item in data) + 1
        else:
            new_id = 1
        
        new_item['id'] = new_id
        data.append(new_item)
        
        with open(JSON_FILE_PATH, 'w', encoding='utf-8') as file:
            json.dump(data, file, ensure_ascii=False, indent=4)
        
        return jsonify(new_item), 201
    except Exception as e:
        return jsonify({"error": str(e)}), 500

if __name__ == '__main__':
    # 移除 db.create_all()，因为 Flask-Migrate 会处理数据库迁移
    # app.run()
   app.run(debug=True, host='0.0.0.0')